﻿using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
using System.IO;
using System.Text.RegularExpressions;
using OfficeOpenXml;
using Dapper;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
using System.Text;
using System.Diagnostics;

namespace ExcelFileQueryTool
{
    public partial class frmMain : Form
    {
        private int LastFormWidth = -1;

        private const int FORM_DESIGN_WIDTH = 1024;

        private ExcelWorkLogService WorkLogService;


        private static readonly String TXT_FILTER_DEFAULT_TEXT = "match_combine_field:value...";

        private const String SHEETLIST_CHECKBOX_NAME_PREFIX = "chk_sheet_";

        public frmMain()
        {
            InitializeComponent();
            LastFormWidth = this.Width;
            txtFilter.Text = TXT_FILTER_DEFAULT_TEXT;

            this.gboxSheetList.MouseDown += gboxSheetList_MouseDown;
            this.gboxSheetList.MouseUp += gboxSheetList_MouseUp;

            Thread t = new Thread(CheckDBState);
            t.Start();
        }

        private void CheckDBState()
        {
            var isOnline = false;
            var onError = false;

            try
            {
                DatabaseService svc = new DatabaseService(AppConfig.DBConnectionStringWithoutDBName, AppConfig.Database);
                isOnline = svc.IsOnline();

            }
            catch (Exception ex)
            {
                onError = true;
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Warn,
                                Title = "查询数据库连线状态出错",
                                Content = ex.Message
                            });
                //SimpleLogger.Log("info>>>查询数据库连线状态出错：" + ex.Message);
            }

            if (InvokeRequired)
            {
                Invoke(new Action<bool, bool>(SetDbStatus), isOnline, onError);
            }
            else
            {
                SetDbStatus(isOnline, onError);
            }
        }

        private void SetDbStatus(bool isOnline, bool onError)
        {
            if (onError)
            {
                btnDbStatus.Text = AppConfig.DBName + "状态检测出错";
                btnDbStatus.Image = global::ExcelFileQueryTool.Properties.Resources.stop;
                btnDbStatus.Enabled = false;
            }
            else
            {
                if (isOnline)
                {
                    btnDbStatus.Text = "[" + AppConfig.DBName + "]状态<联机>";
                    btnDbStatus.Image = global::ExcelFileQueryTool.Properties.Resources.run;
                }
                else
                {
                    btnDbStatus.Text = "[" + AppConfig.DBName + "]状态<脱机>";
                    btnDbStatus.Image = global::ExcelFileQueryTool.Properties.Resources.stop;
                }

                btnDbStatus.Enabled = true;
            }
        }

        private Point SelectionStartLocation = Point.Empty;

        private void gboxSheetList_MouseDown(object sender, MouseEventArgs e)
        {
            SelectionStartLocation.X = e.X;
            SelectionStartLocation.Y = e.Y;
        }

        private void gboxSheetList_MouseUp(object sender, MouseEventArgs e)
        {
            var x1 = SelectionStartLocation.X;
            var y1 = SelectionStartLocation.Y;
            var x2 = e.X;
            var y2 = e.Y;

            var x = x1 > x2 ? x2 : x1;
            var y = y1 > y2 ? y2 : y1;
            var w = Math.Abs(x1 - x2);
            var h = Math.Abs(y1 - y2);

            var rect = new Rectangle(x, y, w, h);
            foreach (Control ctrl in gboxSheetList.Controls)
            {
                if (!IsSheetListItemControl(ctrl))
                    continue;

                var chk = (CheckBox)ctrl;
                if (rect.Contains(chk.Location) || rect.Contains(chk.Right, chk.Bottom))
                    chk.Checked = !chk.Checked;
            }

            SelectionStartLocation.X = 0;
            SelectionStartLocation.Y = 0;
        }

        private bool IsExcelFileReady()
        {
            return File.Exists(txtFile.Text);
        }

        private void btnChooseFile_Click(object sender, EventArgs e)
        {
            ChooseFile();
        }

        private void txtFile_DoubleClick(object sender, EventArgs e)
        {
            ChooseFile();
        }

        private void ChooseFile()
        {
            using (OpenFileDialog diag = new OpenFileDialog())
            {
                diag.Filter = AppConfig.ClientFileChooseFilter;

                // 初始化打开文件对话框的初始文件夹
                var folder = Utility.GetFirstChildFolderFromParent(AppConfig.SourceFolder, "日记");
                if (string.IsNullOrEmpty(folder) == false)
                {
                    string subFolderName = DateTime.Today.Year.ToString();
                    string subFolder = Path.Combine(folder, subFolderName);

                    if (Directory.Exists(subFolder))
                        diag.InitialDirectory = subFolder;
                    else
                        diag.InitialDirectory = folder;
                }

                if (diag.ShowDialog(this) == DialogResult.OK)
                    txtFile.Text = diag.FileName;
            }
        }

        private void txtFile_TextChanged(object sender, EventArgs e)
        {
            LoadWorkLogExcel();
        }

        private void LoadWorkLogExcel()
        {
            if (!IsExcelFileReady())
                return;

            try
            {
                using (frmWaiting fw = new frmWaiting(this, null))
                {
                    fw.Show();
                    fw.ResumeDrawing();

                    for (int i = (gboxSheetList.Controls.Count - 1); i >= 0; i--)
                    {
                        if (IsSheetListItemControl(gboxSheetList.Controls[i]))
                            gboxSheetList.Controls.RemoveAt(i);
                    }

                    this.chk_CheckAllSheets.Checked = true;
                    this.dgvData.DataSource = null;

                    WorkLogService = null; // release the previous reference
                    WorkLogService = new ExcelWorkLogService(txtFile.Text.Trim());

                    // todo: there is a bug which sometimes would causing checkbox checked state incorrect, and I have no idea how to solve it
                    foreach (var info in WorkLogService.SheetInfos)
                    {
                        var chkbox = new CheckBox();
                        chkbox.Name = SHEETLIST_CHECKBOX_NAME_PREFIX + info.Name;
                        chkbox.Text = string.Format("{0}#A{1}:M{2}", info.Name, info.DataRowBegin, info.DataRowEnd);
                        chkbox.Enabled = info.HasContentRow;
                        chkbox.Checked = info.HasContentRow && !info.HasRecordedOnBook; // auto checked if sheet was not recorded
                        chkbox.Tag = info.Name;

                        gboxSheetList.Controls.Add(chkbox);

                        if (chkbox.Checked == false)
                            this.chk_CheckAllSheets.Checked = false;
                    }

                    txtFile.BackColor = Color.Yellow;
                    RelocateSheetListItem();
                }
            }
            catch
            {
                txtFile.Text = "";
                throw;
            }
        }

        private void RelocateSheetListItem()
        {
            const int checkboxGapPX = 2;
            int left = gboxSheetList.Left + checkboxGapPX;
            int top = gboxSheetList.Top - 25;

            foreach (Control ctrl in gboxSheetList.Controls)
            {
                if (!IsSheetListItemControl(ctrl))
                    continue;

                ctrl.Left = left;
                ctrl.Top = top;

                left = left + ctrl.Width + checkboxGapPX;

                if ((gboxSheetList.Right - left) < ctrl.Width)
                {
                    left = gboxSheetList.Left + checkboxGapPX;
                    top = top + ctrl.Height + checkboxGapPX;
                }
            }

            var delta = top + 70 - gboxSheetList.Top - gboxSheetList.Height;
            if (delta >= 0)
            {
                gboxSheetList.Height += delta;
                splitPanel_MainForm.SplitterDistance += delta;
            }
        }

        private static bool IsSheetListItemControl(Control ctrl)
        {
            return ctrl.Name.StartsWith(SHEETLIST_CHECKBOX_NAME_PREFIX);
        }

        private void btnGo_Click(object sender, EventArgs e)
        {
            if (!IsExcelFileReady())
                return;

            if (WorkLogService.FileHasChanged)
            {
                MessageDispatcherManager.DispatchMessage(
                    new AppMessage
                    {
                        Level = AppMessageLevel.Info,
                        Time = DateTime.Now,
                        Title = "客户记录操作",
                        Content = "已检测到文件有变动，请重新载入日志数据"
                    }
                );

                return;
            }

            using (var wf = new frmWaiting(this, null))
            {
                wf.Show();
                wf.ResumeDrawing();

                var selectedSheets = new List<String>(5);

                foreach (Control ctrl in gboxSheetList.Controls)
                {
                    if (!IsSheetListItemControl(ctrl))
                        continue;

                    var chk = (CheckBox)ctrl;

                    if (chk.Checked)
                        selectedSheets.Add((string)chk.Tag);
                }

                var keyword = txtFilter.Text.Trim();
                if (keyword == TXT_FILTER_DEFAULT_TEXT)
                    keyword = "";

                var source = WorkLogService.QueryWorkLogItems(selectedSheets, keyword)
                                           .OrderBy(x => x.SheetName, new ExcelWorkLogSheetNameComparer())
                                           .ToList();
                dgvData.DataSource = new SortableBindingList<ExcelWorkLogItem>(source);
            }

            txtFile.BackColor = SystemColors.ControlLightLight;
            txtFilter.BackColor = SystemColors.ControlLightLight;
        }

        private void dgvData_DoubleClick(object sender, EventArgs e)
        {
            if (dgvData.DataSource == null)
                return;

            if (dgvData.SelectedRows == null)
                return;

            var clickEventArgs = e as MouseEventArgs;
            var hitInfo = dgvData.HitTest(clickEventArgs.X, clickEventArgs.Y);
            if (hitInfo.RowIndex == -1 || hitInfo.ColumnIndex >= 13)
                return;

            var selectedName = dgvData.Rows[hitInfo.RowIndex].Cells["客户供应商"].Value.ToString();

            if (selectedName == "石井悦城水泥厂")
            {
                MessageDispatcherManager.DispatchMessage(
                       new AppMessage
                       {
                           Level = AppMessageLevel.Info,
                           Time = DateTime.Now,
                           Title = "查看销售日记列表",
                           Content = selectedName + " 的销售日记列表只能通过菜单进行操作！"
                       }
                   );

                return;
            }

            var specifiedList = dgvData.GetBindableListFromSource<ExcelWorkLogItem>(item => item.客户供应商 == selectedName);

            using (frmClientLogList frm = new frmClientLogList(specifiedList, WorkLogService))
            {
                frm.ShowDialog();
                dgvData.Refresh();
                this.dgvData.RefreshVisibilityByRecordedStatus();
            }
        }

        private void frmMain_Resize(object sender, EventArgs e)
        {
            // form design size:1024x768
            // listSheet:220x120
            // resize: txtFile,txtSqlCommand
            // relocate:btnChooseFile,btnGo,lblSheetList,listSheet
            if (this.Width < FORM_DESIGN_WIDTH)
            {
                this.Width = FORM_DESIGN_WIDTH; // this will cause form resize event,so do a return
                return;
            }

            // resize
            int widthChanged = (this.Width - LastFormWidth);
            txtFile.Width += widthChanged;
            gboxSheetList.Width += widthChanged;

            // relocate
            btnChooseFile.Left += widthChanged;
            btnGo.Left += widthChanged;
            txtFilter.Left += widthChanged;
            lblRefresh.Left += widthChanged;
            lblOpenLogFile.Left += widthChanged;
            lblReset.Left += widthChanged;

            RelocateSheetListItem();

            LastFormWidth = this.Width;
        }

        private void txtFilter_Leave(object sender, EventArgs e)
        {
            if (txtFilter.Text.Trim() == "")
            {
                txtFilter.Text = TXT_FILTER_DEFAULT_TEXT;
            }
            else
            {
                //do nothing
            }
        }

        private void txtFilter_Enter(object sender, EventArgs e)
        {
            ShowFilterForm();
        }

        private void ShowFilterForm()
        {
            if (txtFilter.Text.Trim() == TXT_FILTER_DEFAULT_TEXT)
            {
                txtFilter.Text = "";
            }

            using (var f = new frmExcelWorkLogFilterSetting(txtFilter.Text))
            {
                f.ShowDialog();
                txtFilter.Text = f.FilterString;
            }
        }


        private void txtFilter_TextChanged(object sender, EventArgs e)
        {
            if (txtFilter.Text.Trim() == TXT_FILTER_DEFAULT_TEXT)
                return;

            txtFilter.BackColor = Color.Yellow;
        }

        private void txtFilter_Click(object sender, EventArgs e)
        {
            ShowFilterForm();
        }

        private void chk_CheckAllSheets_CheckedChanged(object sender, EventArgs e)
        {
            RefreshAllSheetsCheckedState();
        }

        private void RefreshAllSheetsCheckedState()
        {
            foreach (Control ctrl in this.gboxSheetList.Controls)
            {
                if (ctrl.Enabled == false)
                    continue;

                if (IsSheetListItemControl(ctrl))
                {
                    ((CheckBox)ctrl).Checked = chk_CheckAllSheets.Checked;
                }
            }
        }

        private void lblRefresh_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            LoadWorkLogExcel();
        }

        private void btnShiJingExport_Click(object sender, EventArgs e)
        {
            var data = this.dgvData.DataSource as IEnumerable<ExcelWorkLogItem>;
            if (data == null)
                return;

            using (var diag = new OpenFileDialog())
            {
                diag.Title = "请选择石井对数表...";
                diag.Filter = "Excel 2007 Files([对数表]石井提单记录*.xlsx) | [对数表]石井提单记录*.xlsx";

                // 初始化打开文件对话框的初始文件夹
                var shijingFolders = Directory.GetDirectories(AppConfig.SourceFolder, "石井", SearchOption.TopDirectoryOnly);
                if (shijingFolders != null && shijingFolders.Length > 0)
                    diag.InitialDirectory = shijingFolders[0];

                if (diag.ShowDialog() == DialogResult.OK)
                {
                    using (frmWaiting fw = new frmWaiting(this, null))
                    {
                        fw.Show();
                        fw.ResumeDrawing();

                        var f = diag.FileName;

                        data = data.Where(item => !item.HasRecordOnBook && item.客户供应商 == "石井悦城水泥厂");

                        using (ExcelPackage package = new ExcelPackage(new FileInfo(f)))
                        {
                            var book = package.Workbook;

                            foreach (var group in data.GroupBy(item => item.日期).OrderBy(g => g.Key))
                            {
                                var targetSheetName = group.Key.ToString("yyyy-M");
                                var sheet = book.Worksheets[targetSheetName];

                                if (sheet == null)
                                {
                                    sheet = book.Worksheets.Copy("tpl", targetSheetName);
                                    var header_row1 = sheet.Cells[1, 1];
                                    header_row1.Value = header_row1.Text.Replace("@date", group.Key.ToString("yyyy年M月"));
                                }

                                foreach (var log in group)
                                {
                                    var cur_row_index = sheet.Dimension.Rows + 1;

                                    // 日期
                                    var cell_date = sheet.Cells[cur_row_index, 1];
                                    cell_date.Style.Numberformat.Format = "yyyy-m-d";
                                    cell_date.Value = log.日期;

                                    // 货物名称
                                    var cell_name = sheet.Cells[cur_row_index, 2];
                                    cell_name.Style.Numberformat.Format = "@";
                                    cell_name.Value = log.货物;

                                    // 单位
                                    var cell_unit = sheet.Cells[cur_row_index, 3];
                                    cell_unit.Style.Numberformat.Format = "@";
                                    cell_unit.Value = log.单位;

                                    // 数量
                                    var cell_amount = sheet.Cells[cur_row_index, 4];
                                    cell_amount.Style.Numberformat.Format = "#.##00";
                                    cell_amount.Value = log.数量;

                                    // 运输方式
                                    var cell_shipping = sheet.Cells[cur_row_index, 5];
                                    cell_shipping.Style.Numberformat.Format = "@";
                                    cell_shipping.Value = log.运输方式;

                                    // 备注
                                    var cell_note = sheet.Cells[cur_row_index, 6];
                                    cell_note.Style.Numberformat.Format = "@";
                                    if (log.备注 != null && log.备注.Contains("取"))
                                        cell_note.Value = log.备注;
                                }
                            }

                            package.Save();
                        }

                        WorkLogService.MarkAsRecordedOnBook(data);
                        this.dgvData.RefreshVisibilityByRecordedStatus();
                    }

                    //MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK);
                    MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "石井进货记录导出操作",
                                Content = "导出成功！"
                            });
                }
            }
        }

        private void btnFileMgnt_Click(object sender, EventArgs e)
        {
            new frmClientFileDetail().Show(this);
        }

        private void btnFileValidation_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show(this, "确定进行<数据校验>操作吗？", "提示", MessageBoxButtons.YesNo) != DialogResult.Yes)
                return;

            #region Get data from files

            var files = Directory.GetFiles(AppConfig.SourceFolder, AppConfig.FilePattern, SearchOption.AllDirectories);

            List<ClientData> data_from_file = new List<ClientData>();
            var processor = new ClientFileConclusionProcessor();

            for (int i = 0; i < files.Count(); i++)
            {
                var f = files.ElementAt(i);

                if (AppConfig.ExcludeFileNames.Any(exclude => f.EndsWith(exclude)))
                    continue;

                var client = processor.Process(f);

                if (client != null)
                    data_from_file.Add(client);
            }

            #endregion

            #region Get data from db

            string sql = @"select ClientName as Name,sum(TotalMoney) as MaterialUnpaid,max(SalesDate)as LastTradeDate
                            from Bill_Sales
                            group by ClientName";

            IEnumerable<ClientData> data_from_db;

            using (SqlConnection conn = new SqlConnection(AppConfig.DBConnectionString))
            {
                data_from_db = SqlMapper.Query<ClientData>(conn, sql);
            }

            #endregion

            string summary_file = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "summary" + DateTime.Now.ToString("yyMMddHHmmssfff") + ".xlsx");
            using (ExcelPackage p = new ExcelPackage(new FileInfo(summary_file)))
            {
                var not_in_db = data_from_file.Where(x => data_from_db.Any(y => y.Name == x.Name) == false);
                var not_in_file = data_from_db.Where(x => data_from_file.Any(y => y.Name == x.Name) == false);
                var both = data_from_file.Where(x => data_from_db.Any(y => y.Name == x.Name));

                int rowIdx = 2;
                if (p.Workbook.Worksheets.Any(x => x.Name == "result_both"))
                    p.Workbook.Worksheets.Delete("result_both");

                var sheet_both = p.Workbook.Worksheets.Add("result_both");
                sheet_both.Cells[1, 1].Value = "name";
                sheet_both.Cells[1, 2].Value = "file";
                sheet_both.Cells[1, 3].Value = "db";
                sheet_both.Cells[1, 3].Value = "diff";
                foreach (var item in both)
                {
                    sheet_both.Cells[rowIdx, 1].Value = item.Name;
                    sheet_both.Cells[rowIdx, 2].Value = data_from_file.First(x => x.Name == item.Name).UnpaidAmount;
                    sheet_both.Cells[rowIdx, 3].Value = data_from_db.First(x => x.Name == item.Name).UnpaidAmount;
                    sheet_both.Cells[rowIdx, 4].Formula = string.Format("B{0}-C{0}", rowIdx);
                    rowIdx++;
                }

                rowIdx = 2;
                if (p.Workbook.Worksheets.Any(x => x.Name == "result_not_in_db"))
                    p.Workbook.Worksheets.Delete("result_not_in_db");
                var sheet_not_in_db = p.Workbook.Worksheets.Add("result_not_in_db");
                sheet_not_in_db.Cells[1, 1].Value = "name";
                sheet_not_in_db.Cells[1, 2].Value = "file";
                sheet_not_in_db.Cells[1, 3].Value = "db";
                foreach (var item in not_in_db)
                {
                    sheet_not_in_db.Cells[rowIdx, 1].Value = item.Name;
                    sheet_not_in_db.Cells[rowIdx, 2].Value = data_from_file.First(x => x.Name == item.Name).UnpaidAmount;
                    sheet_not_in_db.Cells[rowIdx, 3].Value = "--";
                    rowIdx++;
                }

                rowIdx = 2;
                if (p.Workbook.Worksheets.Any(x => x.Name == "result_not_in_file"))
                    p.Workbook.Worksheets.Delete("result_not_in_file");
                var sheet_not_in_file = p.Workbook.Worksheets.Add("result_not_in_file");
                sheet_not_in_file.Cells[1, 1].Value = "name";
                sheet_not_in_file.Cells[1, 2].Value = "file";
                sheet_not_in_file.Cells[1, 3].Value = "db";
                foreach (var item in not_in_file)
                {
                    sheet_not_in_file.Cells[rowIdx, 1].Value = item.Name;
                    sheet_not_in_file.Cells[rowIdx, 2].Value = "--";
                    sheet_not_in_file.Cells[rowIdx, 3].Value = data_from_db.First(x => x.Name == item.Name).UnpaidAmount;
                    rowIdx++;
                }

                p.Save();
            }

            //MessageBox.Show("数据校验完成!");
            MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "DB & File 数据校验操作",
                                Content = "数据校验完成！"
                            });
        }

        private void btnFindNoBill_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show(this, "确定进行<查找无签单记录>操作吗？", "提示", MessageBoxButtons.YesNo) != DialogResult.Yes)
                return;

            DateTime? begin;
            using (var f = new frmDatePicker(this.Location, "开始日期"))
            {
                f.ShowDialog();
                begin = f.SelectedValue;
            }

            DateTime? end;
            using (var f = new frmDatePicker(this.Location, "截至日期"))
            {
                f.ShowDialog();
                end = f.SelectedValue;
            }

            string savingFile = null;
            using (SaveFileDialog diag = new SaveFileDialog())
            {
                diag.Filter = AppConfig.ClientFileChooseFilter;
                savingFile = diag.FileName;

                if (diag.ShowDialog() == DialogResult.OK)
                    savingFile = diag.FileName;
            }

            if (string.IsNullOrEmpty(savingFile))
            {
                //MessageBox.Show("操作失败，请选择保存路径！", "提示");
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "查找无单号记录",
                                Content = "操作失败，请选择保存路径！"
                            });
                return;
            }

            using (var fw = new frmWaiting(this, null))
            {
                fw.Show();
                fw.ResumeDrawing();

                var files = Directory.GetFiles(AppConfig.SourceFolder, AppConfig.FilePattern, SearchOption.AllDirectories);
                if (files == null || files.Length == 0)
                    return;

                var peopleSvc = new CrmPeopleService(AppConfig.DBConnectionString);

                using (ExcelPackage p = new ExcelPackage(new FileInfo(savingFile)))
                {
                    var sheet = p.Workbook.Worksheets.Add("no_bill_sales");
                    sheet.Cells[1, 1].Value = "客户名称";
                    sheet.Cells[1, 2].Value = "日期";
                    sheet.Cells[1, 3].Value = "项目";
                    sheet.Cells[1, 4].Value = "备注";

                    foreach (var f in files)
                    {
                        if (f.Contains("供应商") || f.Contains("司机"))
                            continue;

                        if (AppConfig.ExcludeFileNames.Any(exclude => f.EndsWith(exclude)))
                            continue;

                        if (begin.HasValue)
                        {
                            // 如果文件的最后修改时间早于指定的时间范围，则可以忽略
                            // 因为数据记录的时间肯定是早于或等于文件修改时间的，不可能先录入未来的数据
                            var info = new FileInfo(f);
                            if (info.LastWriteTime < begin.Value)
                                continue;
                        }

                        var fileSvc = new ClientFileService(f);

                        if (AppConfig.IsClientBillRequireded(fileSvc.ClientName) == false)
                            continue;

                        var list = fileSvc.GetRecordList(peopleSvc, begin, end);
                        if (list == null || list.Count() == 0)
                            continue;

                        Func<BillSales, bool> filter = bill =>
                        {
                            return bill.ProductName.Contains("来款") == false
                                && bill.ProductName.Contains("去款") == false
                                && bill.ProductName.Contains("优惠") == false
                                && bill.ProductName.Contains("提成") == false
                                && bill.ProductName.Contains("损失") == false
                                && string.IsNullOrWhiteSpace(bill.BillNumber);
                        };

                        foreach (var group in list.GroupBy(x => x.ClientName))
                        {
                            foreach (var no_bill in group.Where(filter))
                            {
                                var rowIdx = sheet.Dimension.Rows + 1;
                                sheet.Cells[rowIdx, 1].Value = no_bill.ClientName;
                                sheet.Cells[rowIdx, 2].Value = no_bill.SalesDate;
                                sheet.Cells[rowIdx, 3].Value = no_bill.ProductName + no_bill.ProductAmount.ToString() + no_bill.ProductUnit;
                                sheet.Cells[rowIdx, 4].Value = no_bill.Note;
                            }
                        }
                    }

                    p.Save();
                }
            }

            //MessageBox.Show("操作完成！", "提示");
            MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "查找无单号记录",
                                Content = "操作完成！"
                            });
        }

        private void btnGenerateReport_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show(this, "确定进行<客户汇总报告生成>操作吗？", "提示", MessageBoxButtons.YesNo) != DialogResult.Yes)
                return;

            var sessionId = DateTime.Now.ToString();

            using (var wf = new frmWaiting(this, null))
            {
                wf.Show();
                wf.ResumeDrawing();

                #region Get Client Data

                var files = Directory.GetFiles(AppConfig.SourceFolder, AppConfig.FilePattern, SearchOption.AllDirectories);
                List<ClientData> clientList = new List<ClientData>();
                ClientFileConclusionProcessor processor = new ClientFileConclusionProcessor();

                Action<object> process = (obj) =>
                {
                    var clist = new List<ClientData>();

                    foreach (var f in (string[])obj)
                    {
                        if (AppConfig.ExcludeFileNames.Any(exclude => f.EndsWith(exclude)))
                        {
                            MessageDispatcherManager.DispatchMessage(
                                new AppMessage
                                {
                                    DispatchSessionId = sessionId,
                                    Time = DateTime.Now,
                                    Level = AppMessageLevel.Warn,
                                    Title = "客户文件汇总信息生成操作",
                                    Content = "文件在排除列表中：" + f
                                });
                            continue;
                        }

                        var info = new FileInfo(f);

                        if (info.LastWriteTime < AppConfig.ModifiedTimeAfter)
                        {
                            MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                DispatchSessionId = sessionId,
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Warn,
                                Title = "客户文件汇总信息生成操作",
                                Content = "文件最后修改日期不符合条件：" + f
                            });
                            continue;
                        }

                        if (processor.IsSupport(f))
                        {
                            var client = new ClientData();

                            client.Name = Path.GetFileNameWithoutExtension(f).Replace("[对数表]", string.Empty);
                            var conclusionCache = ClientConclusionCacheService.Get(client.Name);

                            var isCacheAvailable = conclusionCache != null && info.LastWriteTime.Equals(conclusionCache.FileLastModifiedTime);

                            if (isCacheAvailable)
                            {
                                client.UnpaidAmount = 0;
                            }
                            else
                            {
                                client = processor.Process(f);

                                if (client != null)
                                {
                                    clist.Add(client);

                                    #region Add or delete cache

                                    if (client.IsPaymentClear)
                                    {
                                        if (conclusionCache == null)
                                            conclusionCache = new ClientConclusionData();

                                        conclusionCache.FileLastModifiedTime = info.LastWriteTime;
                                        conclusionCache.GeneratedTime = DateTime.Now;
                                        conclusionCache.Name = client.Name;
                                        conclusionCache.UnpaidAmount = client.UnpaidAmount;

                                        ClientConclusionCacheService.Save(conclusionCache);
                                    }
                                    else
                                    {
                                        if (conclusionCache != null)
                                        {
                                            ClientConclusionCacheService.Delete(client.Name);
                                        }
                                    }

                                    #endregion
                                }
                                else
                                {
                                    MessageDispatcherManager.DispatchMessage(
                                        new AppMessage
                                        {
                                            DispatchSessionId = sessionId,
                                            Time = DateTime.Now,
                                            Level = AppMessageLevel.Warn,
                                            Title = "客户文件汇总信息生成操作",
                                            Content = "文件解释失败(return null)：" + f
                                        });
                                }
                            }
                        }
                        else
                        {
                            MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                DispatchSessionId = sessionId,
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Warn,
                                Title = "客户文件汇总信息生成操作",
                                Content = "文件不受支持：" + f
                            });
                        }
                    }

                    lock (typeof(frmMain))
                    {
                        clientList.AddRange(clist);
                    }
                };

                const int pieces = 2;
                var block_size = files.Length / pieces;
                var left = files.Length % pieces;
                List<Task> tasks = new List<Task>(pieces + 1);

                for (int i = 0; i < pieces; i++)
                {
                    var cur_files = files.Skip(i * block_size).Take(block_size).ToArray();
                    tasks.Add(Task.Factory.StartNew(process, cur_files));
                }

                if (left > 0)
                {
                    var cur_files = files.Skip(pieces * block_size).Take(left).ToArray();
                    tasks.Add(Task.Factory.StartNew(process, cur_files));
                }


                Task.WaitAll(tasks.ToArray());
                #endregion

                ClientConclusionCacheService.FlushToFile();

                #region Save Client Data

                string targetFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format("客户汇总报告_{0}.xlsx", DateTime.Now.ToString("yyMMddHHmmssfff")));

                // backup old file
                if (File.Exists(targetFile))
                {
                    int insertIdx = targetFile.LastIndexOf('.');
                    string insertText = "_bak_" + DateTime.Now.ToString("yyMMddHHmmssfff");
                    string newFile = targetFile.Insert(insertIdx, insertText);
                    File.Move(targetFile, newFile);
                }

                using (var package = new ExcelPackage(new FileInfo(targetFile)))
                {
                    var book = package.Workbook;
                    var sheet = book.Worksheets.Add("客户欠款概况表");
                    sheet.HeaderFooter.OddHeader.CenteredText = "客户欠款概况表";

                    sheet.HeaderFooter.OddHeader.LeftAlignedText = Environment.NewLine + "报告生成时间:" + DateTime.Today.ToString("yyyy/M/d");
                    sheet.HeaderFooter.OddHeader.RightAlignedText = ExcelHeaderFooter.PageNumber + "/" + ExcelHeaderFooter.NumberOfPages + " 页";

                    sheet.Cells.Style.Font.Size = 14f;

                    sheet.View.FreezePanes(2, 1); // freeze top row

                    sheet.PrinterSettings.RepeatRows = new ExcelAddress("1:1"); // set top row as print title
                    sheet.PrinterSettings.TopMargin = 0.7m; //1.9cm
                    sheet.PrinterSettings.LeftMargin = 0.6m; //1.4cm
                    sheet.PrinterSettings.BottomMargin = 0.3m; //0.8cm
                    sheet.PrinterSettings.RightMargin = 0.2m; //0.4cm
                    sheet.PrinterSettings.HeaderMargin = 0.3m; //0.8cm
                    sheet.PrinterSettings.FooterMargin = 0.3m; //0.8cm
                    sheet.PrinterSettings.PaperSize = ePaperSize.A4;

                    #region Set Header Row

                    //sheet.View.FreezePanes(1, 1); // may cause file corruption

                    // create header cells
                    var nameHeaderCell = sheet.Cells[1, 1];
                    var materialUnpaidHeaderCell = sheet.Cells["B1:C1"];
                    var phoneHeaderCell = sheet.Cells[1, 4];
                    var personHeaderCell = sheet.Cells[1, 5];
                    var lastbuydateHeaderCell = sheet.Cells[1, 6];
                    var noteHeaderCell = sheet.Cells[1, 7];
                    var typeHeaderCell = sheet.Cells[1, 8];

                    // set header cells value
                    nameHeaderCell.Value = "姓名";
                    materialUnpaidHeaderCell.Value = "欠款";
                    phoneHeaderCell.Value = "联系方式";
                    personHeaderCell.Value = "联系人";
                    lastbuydateHeaderCell.Value = "最后交易";
                    noteHeaderCell.Value = "备注";
                    typeHeaderCell.Value = "类型";

                    materialUnpaidHeaderCell.Merge = true;

                    nameHeaderCell.Style.Font.Bold = true;
                    materialUnpaidHeaderCell.Style.Font.Bold = true;
                    phoneHeaderCell.Style.Font.Bold = true;
                    personHeaderCell.Style.Font.Bold = true;
                    lastbuydateHeaderCell.Style.Font.Bold = true;
                    noteHeaderCell.Style.Font.Bold = true;
                    typeHeaderCell.Style.Font.Bold = true;

                    #endregion

                    #region set column width

                    sheet.Column(1).Width = 23d;
                    sheet.Column(2).Width = 19d;
                    sheet.Column(3).Width = 13d;
                    sheet.Column(4).Width = 23d;
                    sheet.Column(6).Width = 15d;

                    #endregion


                    int rowIdx = 2;

                    // 将死数记录排到最后
                    var deadList = clientList.Where(x => string.IsNullOrEmpty(x.ContactPerson) == false && x.ContactPerson.Contains("死数")).OrderByDescending(x => x.UnpaidAmount);
                    var headList = clientList.Except(deadList).OrderByDescending(x => x.UnpaidAmount);
                    clientList = headList.Concat(deadList).ToList();

                    foreach (var client in clientList)
                    {
                        if (client.IsPaymentClear)
                            continue;

                        client.Name = AppConfig.GetMappedName(client.Name);

                        #region Set Data Row

                        // column A
                        var nameCell = sheet.Cells[rowIdx, 1];
                        nameCell.Value = client.Name;
                        nameCell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
                        nameCell.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;

                        // column B/C
                        var materialUnpaidCell = sheet.Cells[string.Format("B{0}:C{0}", rowIdx)];
                        materialUnpaidCell.Merge = true;
                        materialUnpaidCell.Style.Numberformat.Format = "0.00#";
                        materialUnpaidCell.Value = client.UnpaidAmount;
                        materialUnpaidCell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
                        materialUnpaidCell.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
                        materialUnpaidCell.Style.Font.Bold = true;

                        // column D
                        if (string.IsNullOrEmpty(client.Phone) == false)
                        {
                            var phoneCell = sheet.Cells[rowIdx, 4];
                            phoneCell.Value = string.Join(Environment.NewLine, client.Phone.Split('/'));
                            phoneCell.Style.WrapText = true;
                            phoneCell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
                            phoneCell.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
                        }

                        // column E
                        var personCell = sheet.Cells[rowIdx, 5];
                        personCell.Value = client.ContactPerson;

                        // column F
                        if (client.LastTradeDate.HasValue)
                        {
                            var lastbuydateCell = sheet.Cells[rowIdx, 6];
                            lastbuydateCell.Value = client.LastTradeDate.Value;
                            lastbuydateCell.Style.Numberformat.Format = "yyyy/m/d";
                            lastbuydateCell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
                            lastbuydateCell.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
                        }

                        // column G
                        var noteCell = sheet.Cells[rowIdx, 7];
                        noteCell.Value = client.Note;

                        // column H
                        var typeCell = sheet.Cells[rowIdx, 8];
                        typeCell.Value = client.ClientType;

                        // 如果只有1个part，无需列出来
                        if (AppConfig.IsClientReportDetailRequireded(client.Name))
                        {
                            if (client.TradeParts != null && client.TradeParts.Count > 1)
                            {
                                var cells = sheet.Cells[string.Format("A{0}:A{1}", rowIdx, rowIdx + client.TradeParts.Count)];
                                cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
                                cells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
                                cells.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Dashed);
                                cells.Merge = true;

                                foreach (var part in client.TradeParts)
                                {
                                    rowIdx++;
                                    sheet.Cells[rowIdx, 2].Value = part.PartItem;
                                    sheet.Cells[rowIdx, 3].Value = part.PartTotal;

                                    if (string.IsNullOrEmpty(part.PartPhone) == false && part.PartPhone != client.Phone)
                                    {
                                        sheet.Cells[rowIdx, 4].Value = part.PartPhone;
                                        sheet.Cells[rowIdx, 5].Value = part.PartContact;
                                    }
                                }
                            }
                        }

                        rowIdx++;

                        #endregion
                    }

                    sheet.Cells.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Hair;
                    sheet.Cells.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Hair;
                    sheet.Cells.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Hair;
                    sheet.Cells.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Hair;

                    package.Save();
                }

                #endregion
            }

            //MessageBox.Show("客户汇总报告生成完成！", "提示");
            MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                DispatchSessionId = sessionId,
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "客户汇总报告生成操作",
                                Content = "客户汇总报告生成完成！"
                            });
        }

        private void btnSalesAdd_Click(object sender, EventArgs e)
        {
            using (var f = new frmSalesDetail())
            {
                f.ShowDialog(this);
            }
        }

        private void btnSalesImport_Click(object sender, EventArgs e)
        {
            //if (MessageBox.Show("确认执行客户文件导入DB操作吗(MAYBE SLOWLLY)?", "确认", MessageBoxButtons.YesNo) != System.Windows.Forms.DialogResult.Yes)
            //    return;

            string[] files = null;
            using (OpenFileDialog diag = new OpenFileDialog())
            {
                diag.Multiselect = true;
                diag.Filter = AppConfig.ClientFileChooseFilter;

                if (diag.ShowDialog(this) == DialogResult.OK)
                    files = diag.FileNames;
            }

            if (files == null || files.Length == 0)
                return;

            using (var wf = new frmWaiting(this, null))
            {
                wf.Show();
                wf.ResumeDrawing();

                Func<BillSales, BillSales, bool> predicate1 = (BillSales x, BillSales y) =>
                {
                    // 只要其中一个单号为空，该判断条件就不参与整体的相等判断，只有两者不为空时才参与。
                    // 这是因为如果数据不同步，其中一边又单号，另一边还没录入或因某些失误导致录入失败，就会造成误判
                    return string.IsNullOrEmpty(x.BillNumber)
                        || string.IsNullOrEmpty(y.BillNumber)
                        || x.BillNumber == y.BillNumber;
                };

                Func<BillSales, BillSales, bool> predicate2 = (BillSales x, BillSales y) =>
                {
                    return x.SalesDate.Equals(y.SalesDate)
                        && x.ProductName == y.ProductName
                        && x.ProductUnit == y.ProductUnit
                        && Math.Abs(x.TotalMoney) == Math.Abs(y.TotalMoney)
                        && x.Note == y.ShippingAddress;
                };

                foreach (var f in files)
                {
                    var fileSvc = new ClientFileService(f);
                    var peopleSvc = new CrmPeopleService(AppConfig.DBConnectionString);
                    var list_in_file = (List<BillSales>)fileSvc.GetRecordList(peopleSvc);
                    if (list_in_file == null && list_in_file.Count() == 0)
                        continue;

                    var salesSvc = new BillSalesService(AppConfig.DBConnectionString);
                    var list_in_db = salesSvc.GetListByClientName(fileSvc.ClientName);

                    // 剔除数据库已保存有的数据
                    if (list_in_db != null && list_in_db.Count() > 0)
                    {
                        // date/prodcut/unit/totalmoney/billNO/note
                        foreach (var item_db in list_in_db)
                        {
                            var item_found = list_in_file.Find(x => predicate1(x, item_db) && predicate2(x, item_db));

                            if (item_found != null)
                                list_in_file.Remove(item_found);
                        }
                    }

                    if (list_in_file.Count() > 0)
                        salesSvc.Add(list_in_file);
                }
            }

            //MessageBox.Show("导入成功！", "提示");
            MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "客户销售记录导入操作",
                                Content = "导入成功！"
                            });
        }

        private void btnSalesMgnt_Click(object sender, EventArgs e)
        {
            using (Form f = new frmClientSalesList())
            {
                f.ShowDialog();
            }
        }

        private void btnClientMgnt_Click(object sender, EventArgs e)
        {
            using (var f = new frmClientList())
            {
                f.ShowDialog();
            }
        }

        private void btnDbStatus_Click(object sender, EventArgs e)
        {
            DatabaseService svc = new DatabaseService(AppConfig.DBConnectionStringWithoutDBName, AppConfig.Database);

            if (btnDbStatus.Text.Contains("联机")) // current state is online
            {
                svc.SetOffline();
                SetDbStatus(false, false);

                //MessageBox.Show("数据库[" + AppConfig.Database + "]脱机成功！", "提示", MessageBoxButtons.OK);
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "数据库连接状态操作",
                                Content = "数据库[" + AppConfig.Database + "]脱机成功！"
                            });
            }
            else if (btnDbStatus.Text.Contains("脱机")) // current state is offline
            {
                svc.SetOnline();
                SetDbStatus(true, false);

                //MessageBox.Show("数据库[" + AppConfig.Database + "]联机成功！", "提示", MessageBoxButtons.OK);
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "数据库连接状态操作",
                                Content = "数据库[" + AppConfig.Database + "]联机成功！"
                            });
            }
            else
            {
                // do nothing
            }
        }

        private void btnClientFix_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show(this, "确定进行<客户类型修正>操作吗？", "提示", MessageBoxButtons.YesNo) != DialogResult.Yes)
                return;

            using (var wf = new frmWaiting(this, null))
            {
                wf.Show();
                wf.ResumeDrawing();

                var files = Directory.GetFiles(AppConfig.SourceFolder, AppConfig.FilePattern, SearchOption.AllDirectories);
                var peopleSvc = new CrmPeopleService(AppConfig.DBConnectionString);

                foreach (var f in files)
                {
                    if (AppConfig.ExcludeFileNames.Any(exclude => f.EndsWith(exclude)))
                        continue;

                    var cname = ClientFileService.GetClientNameFromFile(f);
                    var client = peopleSvc.GetByName(cname);
                    if (client == null)
                        continue;

                    var ctype = ClientFileService.GetClientTypeFromFile(f);
                    if (ctype != client.TypeCode)
                        client.TypeCode = ctype;

                    peopleSvc.Update(client);
                }
            }

            //MessageBox.Show("客户类型修正操作完成！", "提示");
            MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "客户类型修正操作",
                                Content = "客户类型修正操作完成！"
                            });
        }

        private void btnDriverMonthlyBill_Click(object sender, EventArgs e)
        {
            if (WorkLogService == null)
            {
                //MessageBox.Show("操作失败，请选择日志文件！", "提示");
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "司机月账单生成操作",
                                Content = "操作失败，请选择日志文件！"
                            });
                return;
            }

            string driver = null;

            using (var f = new frmInputBox())
            {
                if (f.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    driver = f.Text;
                else
                    return;
            }

            if (string.IsNullOrEmpty(driver))
            {
                //MessageBox.Show("操作失败，请输入司机姓名！", "提示");
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "司机月账单生成操作",
                                Content = "操作失败，请输入司机姓名！"
                            });
                return;
            }

            var config = new ExcelWorkLogFilterConfig
            {
                CombineMethod = ExcelWorkLogFilterCombineMethod.Or,
                MatchMethod = ExcelWorkLogFilterMatchMethod.Like
            };

            config.FieldConfigs.Add(new ExcelWorkLogFilterFieldConfig { Field = ExcelWorkLogFilterField.Name, Value = driver });
            config.FieldConfigs.Add(new ExcelWorkLogFilterFieldConfig { Field = ExcelWorkLogFilterField.Driver, Value = driver });
            config.FieldConfigs.Add(new ExcelWorkLogFilterFieldConfig { Field = ExcelWorkLogFilterField.Pay, Value = driver });

            var keyword = WorkLogService.WorkLogFilter.GenerateKeyword(config);
            var sheets = WorkLogService.SheetInfos.Select(x => x.Name);

            var logs = WorkLogService.QueryWorkLogItems(sheets, keyword);
            if (logs == null || logs.Count() == 0)
            {
                //MessageBox.Show("操作失败，没有可操作的数据！", "提示");
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "司机月账单生成操作",
                                Content = "操作失败，没有可操作的数据！"
                            });
                return;
            }

            string savingFile = null;
            using (SaveFileDialog diag = new SaveFileDialog())
            {
                diag.Filter = AppConfig.ClientFileChooseFilter;
                savingFile = diag.FileName;

                if (diag.ShowDialog() == DialogResult.OK)
                    savingFile = diag.FileName;
            }

            if (string.IsNullOrEmpty(savingFile))
            {
                //MessageBox.Show("操作失败，请选择保存路径！", "提示");
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "司机月账单生成操作",
                                Content = "操作失败，请选择保存路径！"
                            });
                return;
            }

            #region Process logic

            List<ExcelWorkLogItem> driverLogs = new List<ExcelWorkLogItem>();
            ExcelWorkLogItem previousItem = null;
            decimal currentConcretePurchaseAmountLeft = 0m;
            decimal currentConcretePurchaseShippingFee = 0m;
            DateTime currentConcretePurchaseDate = DateTime.MinValue;
            StringBuilder concreteSalesBuilder = new StringBuilder();
            Regex regex_Note_Amount = new Regex(AppConfig.WorkLogFileNoteAmountRegexPattern);
            string[] shipping_no_validation = { "三轮", "摩托", "手拖", "的士头" };

            // 先按表分组，再对表内数据按行号进行升序排序，保证数据的连贯性，确保同一车的进货/进仓/销售记录能被正确分配
            var groups = logs.Where(x => x.运输方式 != null && shipping_no_validation.All(y => x.运输方式.Contains(y) == false))
                             .GroupBy(x => x.SheetName);

            foreach (var g in groups)
            {
                foreach (var item in g.OrderBy(x => x.RowIndex))
                {
                    // 水泥需要验证进货数量和销售/进仓数量，其它材料跳过验证
                    // 水泥需要对进货/进仓/销售进行项目名称合并操作，其它材料则不需要合并
                    // 水泥的出货方式有 档口/销售/过车
                    switch (item.类型)
                    {
                        case "进货":
                            if (currentConcretePurchaseAmountLeft > 0m)
                            {
                                // 合并
                                if (previousItem.类型 == "进货" && previousItem.货物 == item.货物)
                                    currentConcretePurchaseAmountLeft += item.数量;
                                else
                                    throw new Exception(string.Format("操作终止，前一次水泥进货记录未卸车完毕，记录不完整(post:Sheet={0}, Row={1})", item.SheetName, item.RowIndex));
                            }

                            if (item.货物.EndsWith("水泥"))
                            {
                                currentConcretePurchaseAmountLeft = item.数量;
                                currentConcretePurchaseShippingFee = item.运装卸费 - currentConcretePurchaseAmountLeft * 8m; //note: shipping_fee should take care of previous item
                                currentConcretePurchaseDate = item.日期;
                                concreteSalesBuilder.Append(item.货物.Replace("水泥", "") + currentConcretePurchaseAmountLeft.ToString() + "吨(");
                                if (string.IsNullOrEmpty(item.备注) == false)
                                {
                                    var matches = regex_Note_Amount.Matches(item.备注);
                                    foreach (Match m in matches)
                                    {
                                        decimal amount = Convert.ToDecimal(m.Groups[1].Value);
                                        currentConcretePurchaseAmountLeft -= amount;
                                        concreteSalesBuilder.Append(m.Value.Replace("吨", "") + ",");
                                    }
                                }
                            }
                            break;
                        case "销售":
                            if (item.客户供应商 == driver)
                            {
                                var lx = new ExcelWorkLogItem
                                {
                                    日期 = item.日期,
                                    客户供应商 = driver,
                                    货物 = "代收" + driver + item.货物 + item.数量.ToString() + item.单位 + "x" + item.单价.ToString() + "+" + item.运装卸费.ToString() + "(" + item.运输方式 + ")",
                                    类型 = "代收",
                                    备注 = item.备注
                                };

                                driverLogs.Add(lx);
                            }

                            if (currentConcretePurchaseAmountLeft > 0m)
                            {
                                if (item.货物.Contains("水泥"))
                                {
                                    currentConcretePurchaseAmountLeft -= item.数量;
                                    concreteSalesBuilder.AppendFormat(item.客户供应商 + item.备注 + " " + item.数量.ToString() + ",");
                                }
                                else
                                {
                                    throw new Exception(string.Format("操作终止，前一次水泥进货记录未卸车完毕，记录不完整(post:Sheet={0}, Row={1})", item.SheetName, item.RowIndex));
                                }
                            }
                            else
                            {
                                var l = new ExcelWorkLogItem
                                {
                                    日期 = item.日期,
                                    客户供应商 = driver,
                                    货物 = item.客户供应商 + item.备注 + item.货物 + item.数量.ToString() + item.单位 + "x" + item.单价.ToString() + "[" + item.备注 + "]",
                                    类型 = "提成",
                                    备注 = item.备注
                                };

                                driverLogs.Add(l);
                            }
                            break;
                        case "支出": // 运费/卸车之类的暂时未处理
                            break;
                        case "收入": // 来款之类的暂时未处理
                            break;
                        default:
                            break;
                    }

                    if (item == g.Last() && currentConcretePurchaseAmountLeft > 0m)
                        throw new Exception(string.Format("操作终止，本次水泥进货记录未卸车完毕，记录不完整(current:Sheet={0}, Row={1})", item.SheetName, item.RowIndex));

                    if (currentConcretePurchaseAmountLeft <= 0m && concreteSalesBuilder.Length > 0)
                    {
                        var l = new ExcelWorkLogItem
                        {
                            日期 = currentConcretePurchaseDate,
                            客户供应商 = driver,
                            货物 = concreteSalesBuilder.Remove(concreteSalesBuilder.Length - 1, 1).ToString() + ")",
                            单价 = currentConcretePurchaseShippingFee,
                            类型 = "提成"
                        };

                        driverLogs.Add(l);
                        currentConcretePurchaseShippingFee = 0m;
                        currentConcretePurchaseAmountLeft = 0m;
                        currentConcretePurchaseDate = DateTime.MinValue;
                        concreteSalesBuilder.Clear();
                    }

                    previousItem = item;
                }
            }

            using (ExcelPackage p = new ExcelPackage(new FileInfo(savingFile)))
            {
                var sheet = p.Workbook.Worksheets.Add("driver_log_" + DateTime.Today.ToString("yyMMdd"));
                sheet.Cells[1, 1].Value = "日期";
                sheet.Cells[1, 2].Value = "名称";
                sheet.Cells[1, 3].Value = "类型";
                sheet.Cells[1, 4].Value = "原始金额(元)";
                sheet.Cells[1, 5].Value = "支付比例";
                sheet.Cells[1, 6].Value = "最终金额(元)";
                sheet.Cells[1, 7].Value = "状态";

                var rowIdx = 2;
                var lastMergeRowIdx = 2;
                var currentDate = DateTime.MinValue;

                foreach (var log in driverLogs.OrderBy(x => x.日期))
                {
                    if (currentDate == DateTime.MinValue)
                        currentDate = log.日期;

                    // do merge
                    if (log.日期 != currentDate)
                    {
                        sheet.Cells[string.Format("A{0}:A{1}", lastMergeRowIdx, rowIdx - 1)].Merge = true;

                        currentDate = log.日期;
                        lastMergeRowIdx = rowIdx;
                    }

                    sheet.Cells[rowIdx, 1].Value = log.日期;
                    sheet.Cells[rowIdx, 2].Value = log.货物;
                    sheet.Cells[rowIdx, 3].Value = log.类型;
                    sheet.Cells[rowIdx, 4].Value = log.单价;
                    sheet.Cells[rowIdx, 6].Formula = string.Format("D{0}*E{0}", rowIdx);

                    sheet.Cells[rowIdx, 1].Style.Font.Size = 12f;
                    sheet.Cells[rowIdx, 2].Style.Font.Size = 12f;
                    sheet.Cells[rowIdx, 3].Style.Font.Size = 12f;
                    sheet.Cells[rowIdx, 4].Style.Font.Size = 12f;
                    sheet.Cells[rowIdx, 5].Style.Font.Size = 12f;
                    sheet.Cells[rowIdx, 6].Style.Font.Size = 12f;

                    sheet.Cells[rowIdx, 1].Style.Numberformat.Format = "m-d";
                    sheet.Cells[rowIdx, 5].Style.Numberformat.Format = "0%";

                    switch (log.类型)
                    {
                        case "提成":
                            if (log.货物.Contains("石井") || log.货物.Contains("联峰") || log.货物.Contains("华润") || log.货物.Contains("中材"))
                                sheet.Cells[rowIdx, 5].Value = 0.15;
                            else
                                sheet.Cells[rowIdx, 5].Value = 0.13;
                            break;

                        case "支出":
                            sheet.Cells[rowIdx, 5].Value = 1d;
                            break;

                        case "代收":
                            sheet.Cells[rowIdx, 5].Value = -1d;
                            break;

                        default:
                            break;
                    }

                    rowIdx++;
                }

                p.Save();
            }

            //MessageBox.Show("操作完成！", "提示");
            MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "司机月账单生成操作",
                                Content = "操作完成！"
                            });

            #endregion
        }

        private void btnSmallAccountSales_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show(this, "确定进行<提取小额散客记录>操作吗？", "提示", MessageBoxButtons.YesNo) != DialogResult.Yes)
                return;

            DateTime? begin;
            using (var f = new frmDatePicker(this.Location, "开始日期"))
            {
                f.ShowDialog();
                begin = f.SelectedValue;
            }

            DateTime? end;
            using (var f = new frmDatePicker(this.Location, "截至日期"))
            {
                f.ShowDialog();
                end = f.SelectedValue;
            }

            string savingFile = null;
            using (SaveFileDialog diag = new SaveFileDialog())
            {
                diag.Filter = AppConfig.ClientFileChooseFilter;
                savingFile = diag.FileName;

                if (diag.ShowDialog() == DialogResult.OK)
                    savingFile = diag.FileName;
            }

            if (string.IsNullOrEmpty(savingFile))
            {
                //MessageBox.Show("操作失败，请选择保存路径！", "提示");
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "散客销售记录导出操作",
                                Content = "操作失败，请选择保存路径！"
                            });
                return;
            }

            using (var fw = new frmWaiting(this, null))
            {
                fw.Show();
                fw.ResumeDrawing();

                var files = Directory.GetFiles(Path.Combine(AppConfig.SourceFolder, "散客"), AppConfig.FilePattern, SearchOption.AllDirectories);
                if (files == null || files.Length == 0)
                    return;

                var peopleSvc = new CrmPeopleService(AppConfig.DBConnectionString);
                var processor = new ClientFileConclusionProcessor();
                var result_list = new List<BillSales>();

                using (ExcelPackage p = new ExcelPackage(new FileInfo(savingFile)))
                {
                    var sheet = p.Workbook.Worksheets.Add("small");
                    sheet.Cells[1, 1].Value = "客户";
                    sheet.Cells[1, 2].Value = "日期";
                    sheet.Cells[1, 3].Value = "货物";
                    sheet.Cells[1, 4].Value = "数量";
                    sheet.Cells[1, 5].Value = "单位";
                    sheet.Cells[1, 6].Value = "单价";
                    sheet.Cells[1, 7].Value = "卸车";
                    sheet.Cells[1, 8].Value = "运费";
                    sheet.Cells[1, 9].Value = "合计";
                    sheet.Cells[1, 10].Value = "备注";
                    sheet.Cells[1, 11].Value = "单号";

                    foreach (var f in files)
                    {
                        if (AppConfig.ExcludeFileNames.Any(exclude => f.EndsWith(exclude)))
                            continue;

                        var clientOverallInfo = processor.Process(f);
                        if (clientOverallInfo == null)
                            continue;

                        if (clientOverallInfo.UnpaidAmount == 0m)
                            continue;

                        var fileSvc = new ClientFileService(f);
                        var list = fileSvc.GetRecordList(peopleSvc, begin, end);
                        if (list == null || list.Count() == 0)
                            continue;

                        foreach (var sales in list)
                        {
                            var rowIdx = sheet.Dimension.Rows + 1;
                            sheet.Cells[rowIdx, 1].Value = sales.ClientName;
                            sheet.Cells[rowIdx, 2].Value = sales.SalesDate;
                            sheet.Cells[rowIdx, 3].Value = sales.ProductName;
                            sheet.Cells[rowIdx, 4].Value = sales.ProductAmount;
                            sheet.Cells[rowIdx, 5].Value = sales.ProductUnit;
                            sheet.Cells[rowIdx, 6].Value = sales.ProductPrice;
                            sheet.Cells[rowIdx, 7].Value = sales.LoadingFee;
                            sheet.Cells[rowIdx, 8].Value = sales.ShippingAddress;
                            sheet.Cells[rowIdx, 9].Value = sales.TotalMoney;
                            sheet.Cells[rowIdx, 10].Value = sales.Note;
                            sheet.Cells[rowIdx, 11].Value = sales.BillNumber;
                        }
                    }

                    p.Save();
                }

                //MessageBox.Show("操作完成！");
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Info,
                                Title = "散客销售记录导出操作",
                                Content = "操作完成！"
                            });
            }
        }

        private void lblOpenLogFile_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            if (IsExcelFileReady() == false)
                return;

            using (Process.Start(txtFile.Text.Trim())) { }
        }

        private void ChooseFileMenuItem_Click(object sender, EventArgs e)
        {
            var item = (MenuItem)sender;
            var file = item.Tag as string;
            if (string.IsNullOrEmpty(file) == false)
            {
                using (Process p = Process.Start(file)) { }
            }
        }

        private void btnChooseFile_MouseUp(object sender, MouseEventArgs e)
        {
            if (e.Button == System.Windows.Forms.MouseButtons.Right)
            {
                var ctrl = (Button)sender;

                if (ctrl.ContextMenu == null)
                    ctrl.ContextMenu = new ContextMenu();

                var folder = Utility.GetFirstChildFolderFromParent(AppConfig.SourceFolder, "日记");
                if (string.IsNullOrEmpty(folder))
                    return;

                var subFolders = Directory.GetDirectories(folder, "*", SearchOption.TopDirectoryOnly);
                if (subFolders == null || subFolders.Length == 0)
                    return;

                foreach (var sub in subFolders)
                {
                    var files = Directory.GetFiles(sub, "日记_*.xlsx", SearchOption.TopDirectoryOnly);
                    if (files == null || files.Length == 0)
                        continue;

                    var subFolderName = new DirectoryInfo(sub).Name;
                    var topMenuName = sub.GetHashCode().ToString();
                    MenuItem topMenuItem;

                    if (ctrl.ContextMenu.MenuItems.ContainsKey(topMenuName))
                    {
                        topMenuItem = ctrl.ContextMenu.MenuItems[topMenuName];
                    }
                    else
                    {
                        topMenuItem = new MenuItem { Name = topMenuName, Text = subFolderName };
                        ctrl.ContextMenu.MenuItems.Add(topMenuItem);
                    }


                    foreach (var f in files)
                    {
                        var subMenuName = f.GetHashCode().ToString();
                        if (topMenuItem.MenuItems.ContainsKey(subMenuName))
                            continue;

                        var subMenuItem = new MenuItem { Name = subMenuName, Text = Path.GetFileNameWithoutExtension(f), Tag = f };
                        subMenuItem.Click += ChooseFileMenuItem_Click;
                        topMenuItem.MenuItems.Add(subMenuItem);
                    }
                }

                ctrl.ContextMenu.Show(ctrl, e.Location);
            }
        }

        private void btnDialyFile_Click(object sender, EventArgs e)
        {
            using (var f = new frmDialyFile())
            {
                f.ShowDialog();
            }
        }

        private void lblReset_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            txtFile.Text = string.Empty;
            txtFilter.Text = TXT_FILTER_DEFAULT_TEXT;

            for (int i = (gboxSheetList.Controls.Count - 1); i >= 0; i--)
            {
                if (IsSheetListItemControl(gboxSheetList.Controls[i]))
                    gboxSheetList.Controls.RemoveAt(i);
            }

            this.chk_CheckAllSheets.Checked = false;

            dgvData.DataSource = null;
        }

        private void btnEditGridCellValue_Click(object sender, EventArgs e)
        {
            if (dgvData.DataSource == null)
                return;

            if (dgvData.SelectedRows == null)
                return;

            using (var f = new frmEditGridCellValue(WorkLogService, dgvData, new string[] { "货款合计" }))
            {
                f.ShowDialog();
            }

            dgvData.Refresh();
        }

        protected override bool ProcessCmdKey(ref Message msg, Keys keyData)
        {
            switch (keyData)
            {
                case Keys.F2:
                    chk_CheckAllSheets.Checked = !chk_CheckAllSheets.Checked;
                    break;

                case Keys.F3:
                    ShowFilterForm();
                    break;

                case Keys.F5:
                    LoadWorkLogExcel();
                    break;

                default:
                    break;
            }

            return base.ProcessCmdKey(ref msg, keyData);
        }

        private void btnEditHeader_Click(object sender, EventArgs e)
        {
            using (var frm = new frmEditHeader())
            {
                frm.ShowDialog();
            }
        }

        private void btnWorkLogImageProcess_Click(object sender, EventArgs e)
        {
            using (var frm = new frmWorkLogImageProcess())
            {
                frm.ShowDialog();
            }
        }
    }
}
